﻿using MySql.Data.MySqlClient;
using System.ComponentModel;
using System.Data;

namespace OrderManagementSystem
{

    public class DataBase()
    {
        public MySqlConnection GetConnection()
        {
            try
            {
                MySqlConnection connection = new MySqlConnection(
               "datasource=localhost;username=root;password=qun9394601;database=orders_system;charset=utf8mb4");
                connection.Open();
                Console.WriteLine("数据库连接成功！");
                return connection;
            }
            catch (MySqlException ex)
            {
                Console.WriteLine($"连接失败: {ex.Message}");
                throw;
            }
        }

        public BindingList<Order> GetAllOrders()
        {
            var orders = new BindingList<Order>();
            try
            {
                using (MySqlConnection connection = GetConnection())
                {
                    string sql = @"
                SELECT 
                    o.OrderId, o.Customer, 
                    d.ProductName, d.Quantity, d.Price
                FROM orders o
                LEFT JOIN OrderDetails d ON o.OrderId = d.OrderId
                ORDER BY o.OrderId"; 

                    using (MySqlCommand cmd = new MySqlCommand(sql, connection))
                    {
                        
                        using (MySqlDataReader reader = cmd.ExecuteReader())
                        {
                            int currentOrderId = -1;
                            Order currentOrder = null;

                            while (reader.Read())
                            {
                                int orderId = reader.GetInt32("OrderId");

                                if (orderId != currentOrderId)
                                {
                                    currentOrder = new Order
                                    {
                                        OrderId = orderId,
                                        Customer = reader.GetString("Customer")
                                    };
                                    orders.Add(currentOrder);
                                    currentOrderId = orderId;
                                }

                                if (!reader.IsDBNull(reader.GetOrdinal("ProductName")))
                                {
                                    currentOrder.Details.Add(new OrderDetails
                                    {
                                        ProductName = reader.GetString("ProductName"),
                                        Quantity = reader.GetInt32("Quantity"),
                                        Price = reader.GetDecimal("Price")
                                    });
                                }
                            }
                        }
                    }
                }
                return orders;
            }
            catch (MySqlException ex)
            {
                Console.WriteLine($"数据库操作失败: {ex.Message}");
                return new BindingList<Order>();
            }
        }

        public void AddOrder(Order myorder)
        {
            try
            {
                using (MySqlConnection connection = GetConnection())
                {
                    string sql = "INSERT INTO orders (OrderId, Customer) VALUES (@id, @customer)";
                    using (MySqlCommand cmd = new MySqlCommand(sql, connection))
                    {
                        cmd.Parameters.AddWithValue("@id", myorder.OrderId);
                        cmd.Parameters.AddWithValue("@customer", myorder.Customer);
                        cmd.ExecuteNonQuery();
                       
                    }
                }
            }
            catch (MySqlException ex)
            {
                Console.WriteLine($"数据库操作失败: {ex.Message}");
            }
        }

        public void AddOrderDetail(Order myorder,OrderDetails mydetail)
        {
            try
            {
                using (MySqlConnection connection = GetConnection())
                {
                    string sql = "INSERT INTO OrderDetails (OrderId, ProductName, Quantity, Price) VALUES (@id, @product, @quantity, @price)";
                    using (MySqlCommand cmd = new MySqlCommand(sql, connection))
                    {
                        cmd.Parameters.AddWithValue("@id", myorder.OrderId);
                        cmd.Parameters.AddWithValue("@product", mydetail.ProductName);
                        cmd.Parameters.AddWithValue("@quantity", mydetail.Quantity);
                        cmd.Parameters.AddWithValue("@price", mydetail.Price);
                        cmd.ExecuteNonQuery();
                        Console.WriteLine("数据插入成功！");
                    }
                }
            }
            catch (MySqlException ex)
            {
                Console.WriteLine($"数据库操作失败: {ex.Message}");
            }
        }

        public void DeleteOrder(int orderId)
        {
            try
            {
                using (MySqlConnection connection = GetConnection())
                {
                    string sql = "DELETE FROM orders WHERE OrderId = @orderId";
                    using (MySqlCommand cmd = new MySqlCommand(sql, connection))
                    {
                        cmd.Parameters.AddWithValue("@orderId", orderId);
                        cmd.ExecuteNonQuery();
                        Console.WriteLine("订单删除成功！");
                    }
                }
            }
            catch (MySqlException ex)
            {
                Console.WriteLine($"数据库操作失败: {ex.Message}");
            }
            catch (Exception ex)
            {
                Console.WriteLine($"未知错误: {ex.Message}");
            }
        }

        public void DeleteDetail(int orderId)
        {
            try
            {
                using (MySqlConnection connection = GetConnection())
                {
                    string sql = "DELETE FROM orderdetails WHERE OrderId = @orderId";
                    using (MySqlCommand cmd = new MySqlCommand(sql, connection))
                    {
                        cmd.Parameters.AddWithValue("@orderId", orderId);
                        cmd.ExecuteNonQuery();
                        Console.WriteLine("订单删除成功！");
                    }
                }
            }
            catch (MySqlException ex)
            {
                Console.WriteLine($"数据库操作失败: {ex.Message}");
            }
            catch (Exception ex)
            {
                Console.WriteLine($"未知错误: {ex.Message}");
            }
        }

        

        public List<Order> GetOrdersByCustomer(string customerName)
        {
            List<Order> orders = new List<Order>();
            try
            {
                using (MySqlConnection connection = GetConnection())
                {
                    string sql = @"
                SELECT o.OrderId, o.Customer, 
                       d.ProductName, d.Quantity, d.Price
                FROM orders o
                LEFT JOIN OrderDetails d ON o.OrderId = d.OrderId
                WHERE o.Customer = @customerName
                ORDER BY o.OrderId";

                    using (MySqlCommand cmd = new MySqlCommand(sql, connection))
                    {
                        cmd.Parameters.AddWithValue("@customerName", customerName);
                        using (MySqlDataReader reader = cmd.ExecuteReader())
                        {
                            int currentOrderId = -1;
                            Order currentOrder = null;

                            while (reader.Read())
                            {
                                int orderId = reader.GetInt32("OrderId");

                                if (orderId != currentOrderId)
                                {
                                    currentOrder = new Order
                                    {
                                        OrderId = orderId,
                                        Customer = reader.GetString("Customer")
                                    };
                                    orders.Add(currentOrder);
                                    currentOrderId = orderId;
                                }

                                if (!reader.IsDBNull(reader.GetOrdinal("ProductName")))
                                {
                                    var detail = new OrderDetails
                                    {
                                        ProductName = reader.GetString("ProductName"),
                                        Quantity = reader.GetInt32("Quantity"),
                                        Price = reader.GetDecimal("Price")
                                    };

                                    currentOrder.AddDetail(detail);
                                }
                            }
                        }
                    }
                }
                return orders;
            }
            catch (MySqlException ex)
            {
                Console.WriteLine($"数据库操作失败: {ex.Message}");
                return null;
            }
        }
        public Order GetOrderById(int orderId)
        {
            Order order = null;
            try
            {
                using (MySqlConnection connection = GetConnection())
                {
                    // 关联订单主表和明细表（网页10、网页12）
                    string sql = @"
                SELECT o.OrderId, o.Customer,
                       d.ProductName, d.Quantity, d.Price
                FROM orders o
                LEFT JOIN OrderDetails d ON o.OrderId = d.OrderId
                WHERE o.OrderId = @orderId
                ORDER BY d.ProductName"; // 按商品排序

                    using (MySqlCommand cmd = new MySqlCommand(sql, connection))
                    {
                        cmd.Parameters.AddWithValue("@orderId", orderId);
                        using (MySqlDataReader reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                // 单订单只需初始化一次对象（网页1逻辑优化）
                                if (order == null)
                                {
                                    order = new Order
                                    {
                                        OrderId = reader.GetInt32("OrderId"),
                                        Customer = reader.GetString("Customer")
                                    };
                                }

                                // 添加明细（网页3的空值处理）
                                if (!reader.IsDBNull(reader.GetOrdinal("ProductName")))
                                {
                                    order.AddDetail(new OrderDetails
                                    {
                                        ProductName = reader.GetString("ProductName"),
                                        Quantity = reader.GetInt32("Quantity"),
                                        Price = reader.GetDecimal("Price")
                                    });
                                }
                            }
                        }
                    }
                }
                return order;
            }
            catch (MySqlException ex)
            {
                Console.WriteLine($"数据库操作失败: {ex.Message}");
                return null;
            }
        }
        public List<Order> GetOrdersByProduct(string productName)
        {
            List<Order> orders = new List<Order>();
            try
            {
                using (MySqlConnection connection = GetConnection())
                {
                    // INNER JOIN过滤无效记录（网页10、网页14）
                    string sql = @"
                SELECT o.OrderId, o.Customer,
                       d.ProductName, d.Quantity, d.Price
                FROM orders o
                INNER JOIN OrderDetails d ON o.OrderId = d.OrderId
                WHERE d.ProductName = @productName
                ORDER BY o.OrderId"; // 按订单聚合

                    using (MySqlCommand cmd = new MySqlCommand(sql, connection))
                    {
                        cmd.Parameters.AddWithValue("@productName", productName);
                        using (MySqlDataReader reader = cmd.ExecuteReader())
                        {
                            int currentOrderId = -1;
                            Order currentOrder = null;

                            while (reader.Read())
                            {
                                int orderId = reader.GetInt32("OrderId");

                                // 新订单时创建对象（继承网页1的聚合逻辑）
                                if (orderId != currentOrderId)
                                {
                                    currentOrder = new Order
                                    {
                                        OrderId = orderId,
                                        Customer = reader.GetString("Customer")
                                    };
                                    orders.Add(currentOrder);
                                    currentOrderId = orderId;
                                }

                                // 添加匹配的明细（网页12的关联逻辑）
                                currentOrder.AddDetail(new OrderDetails
                                {
                                    ProductName = reader.GetString("ProductName"),
                                    Quantity = reader.GetInt32("Quantity"),
                                    Price = reader.GetDecimal("Price")
                                });
                            }
                        }
                    }
                }
                return orders;
            }
            catch (MySqlException ex)
            {
                Console.WriteLine($"数据库操作失败: {ex.Message}");
                return null;
            }
        }
    }

    public class OrderDetails
    {
        public string ProductName { get; set; }
        public int Quantity { get; set; }
        public decimal Price { get; set; }
        public decimal Subtotal => Quantity * Price;

        public override bool Equals(object obj)
        {
            return obj is OrderDetails details &&
                   ProductName == details.ProductName &&
                   Quantity == details.Quantity &&
                   Price == details.Price;
        }

        public override int GetHashCode()
        {
            return HashCode.Combine(ProductName, Quantity, Price);
        }

        public override string ToString()
        {
            return $"{ProductName} x{Quantity} @{Price:C} (小计: {Subtotal:C})";
        }
    }



    public class Order
    {
        public int OrderId { get; set; }
        public string Customer { get; set; }
        public List<OrderDetails> Details { get; } = new List<OrderDetails>();
        public decimal TotalAmount => Details.Sum(d => d.Subtotal);

        public override bool Equals(object obj)
        {
            return obj is Order order && OrderId == order.OrderId;
        }

        public override int GetHashCode()
        {
            return HashCode.Combine(OrderId);
        }

        public override string ToString()
        {
            return $"订单号: {OrderId}\n客户: {Customer}\n总金额: {TotalAmount:C}\n明细:\n{string.Join("\n", Details.Select((d, i) => $"{i + 1}. {d}"))}";
        }

        public void AddDetail(OrderDetails detail)
        {
            if (Details.Contains(detail))
                throw new ArgumentException("订单明细已存在");
            Details.Add(detail);
        }
    }

    public class OrderService
    {

        public List<Order> orders = new List<Order>();
        public DataBase db = new DataBase();

        // 核心方法
        public void AddOrder(Order order)
        {
            if (orders.Any(o => o.Equals(order)))
                throw new InvalidOperationException("订单已存在");
            orders.Add(order);
        }

        public void RemoveOrder(int orderId)
        {
            var order = orders.FirstOrDefault(o => o.OrderId == orderId);
            if (order == null)
                throw new KeyNotFoundException("订单不存在");
            orders.Remove(order);
        }

        public void UpdateOrder(Order newOrder)
        {
            var index = orders.FindIndex(o => o.OrderId == newOrder.OrderId);
            if (index == -1)
                throw new KeyNotFoundException("订单不存在");
            orders[index] = newOrder;
        }

        public IEnumerable<Order> QueryByOrderId(int orderId) =>
            orders.Where(o => o.OrderId == orderId).OrderBy(o => o.TotalAmount);

        public IEnumerable<Order> QueryByProductName(string productName) =>
            orders.Where(o => o.Details.Any(d => d.ProductName == productName))
                 .OrderBy(o => o.TotalAmount);

        public IEnumerable<Order> QueryByCustomer(string customer) =>
            orders.Where(o => o.Customer == customer)
                 .OrderBy(o => o.TotalAmount);

        public IEnumerable<Order> QueryByAmountRange(decimal min, decimal max) =>
            orders.Where(o => o.TotalAmount >= min && o.TotalAmount <= max)
                 .OrderBy(o => o.TotalAmount);

        public void SortOrders() => orders.Sort((a, b) => a.OrderId.CompareTo(b.OrderId));

        public void SortOrders(Comparison<Order> comparison) => orders.Sort(comparison);

        // 用户交互
        public void AddOrder_()
        {
            try
            {
                var order = new Order();
                Console.Write("输入订单号：");
                order.OrderId = int.Parse(Console.ReadLine());
                Console.Write("输入客户名称：");
                order.Customer = Console.ReadLine();

                while (true)
                {
                    Console.WriteLine("\n添加商品明细（输入N结束）");
                    Console.Write("商品名称：");
                    var name = Console.ReadLine();
                    if (name.ToUpper() == "N") break;

                    var detail = new OrderDetails { ProductName = name };
                    Console.Write("数量：");
                    detail.Quantity = int.Parse(Console.ReadLine());
                    Console.Write("单价：");
                    detail.Price = decimal.Parse(Console.ReadLine());

                    order.AddDetail(detail);
                }

                AddOrder(order);
                Console.WriteLine("订单添加成功！");
            }
            catch (Exception e)
            {
                Console.WriteLine($"操作失败：{e.Message}");
            }
        }

        public void RemoveOrder_()
        {
            try
            {
                Console.Write("输入要删除的订单号：");
                var id = int.Parse(Console.ReadLine());
                RemoveOrder(id);
                Console.WriteLine("订单删除成功！");
            }
            catch (Exception e)
            {
                Console.WriteLine($"操作失败：{e.Message}");
            }
        }

        public void UpdateOrder_()
        {
            try
            {
                Console.Write("输入要修改的订单号：");
                var id = int.Parse(Console.ReadLine());
                var oldOrder = QueryByOrderId(id).FirstOrDefault() ?? throw new KeyNotFoundException();

                var newOrder = new Order { OrderId = id };
                Console.Write($"新客户名称（当前：{oldOrder.Customer}）：");
                var newCustomer = Console.ReadLine();
                newOrder.Customer = string.IsNullOrWhiteSpace(newCustomer) ? oldOrder.Customer : newCustomer;

                Console.WriteLine("重新输入商品明细：");
                while (true)
                {
                    Console.WriteLine("\n添加商品明细（输入N结束）");
                    Console.Write("商品名称：");
                    var name = Console.ReadLine();
                    if (name.ToUpper() == "N") break;

                    var detail = new OrderDetails { ProductName = name };
                    Console.Write("数量：");
                    detail.Quantity = int.Parse(Console.ReadLine());
                    Console.Write("单价：");
                    detail.Price = decimal.Parse(Console.ReadLine());

                    newOrder.AddDetail(detail);
                }

                UpdateOrder(newOrder);
                Console.WriteLine("订单修改成功！");
            }
            catch (Exception e)
            {
                Console.WriteLine($"操作失败：{e.Message}");
            }
        }

        public void QueryOrders_()
        {
            Console.WriteLine("\n查询方式：");
            Console.WriteLine("1. 按订单号");
            Console.WriteLine("2. 按商品名称");
            Console.WriteLine("3. 按客户名称");
            Console.WriteLine("4. 按金额范围");
            Console.Write("请选择查询方式：");

            IEnumerable<Order> results = null;
            switch (Console.ReadLine())
            {
                case "1":
                    Console.Write("输入订单号：");
                    results = QueryByOrderId(int.Parse(Console.ReadLine()));
                    break;

                case "2":
                    Console.Write("输入商品名称：");
                    results = QueryByProductName(Console.ReadLine());
                    break;

                case "3":
                    Console.Write("输入客户名称：");
                    results = QueryByCustomer(Console.ReadLine());
                    break;

                case "4":
                    Console.Write("输入最小金额：");
                    var min = decimal.Parse(Console.ReadLine());
                    Console.Write("输入最大金额：");
                    results = QueryByAmountRange(min, decimal.Parse(Console.ReadLine()));
                    break;

                default:
                    Console.WriteLine("无效选择");
                    return;
            }

            Console.WriteLine("\n查询结果：");
            if (results.Any())
                foreach (var order in results)
                    Console.WriteLine(order + "\n");
            else
                Console.WriteLine("未找到匹配订单");
        }

        public void SortOrders_()
        {
            Console.WriteLine("排序方式：");
            Console.WriteLine("1. 按订单号（默认）");
            Console.WriteLine("2. 按总金额");
            Console.Write("请选择：");

            var choice = Console.ReadLine();
            if (choice == "2")
                SortOrders((a, b) => a.TotalAmount.CompareTo(b.TotalAmount));
            else
                SortOrders();

            Console.WriteLine("排序完成");
        }
    }

    internal class Program
    {
        private static OrderService service = new OrderService();

        private static void Main()
        {
            bool exit = false;
            while (!exit)
            {
                Console.WriteLine("\n订单管理系统");
                Console.WriteLine("1. 添加订单");
                Console.WriteLine("2. 删除订单");
                Console.WriteLine("3. 修改订单");
                Console.WriteLine("4. 查询订单");
                Console.WriteLine("5. 排序订单");
                Console.WriteLine("6. 退出");
                Console.Write("请选择操作：");

                switch (Console.ReadLine())
                {
                    case "1":
                        service.AddOrder_();
                        break;

                    case "2":
                        service.RemoveOrder_();
                        break;

                    case "3":
                        service.UpdateOrder_();
                        break;

                    case "4":
                        service.QueryOrders_();
                        break;

                    case "5":
                        service.SortOrders_();
                        break;

                    case "6":
                        exit = true;
                        break;

                    default:
                        Console.WriteLine("无效输入");
                        break;
                }
            }
            return;
        }
    }
}